# -*- coding: utf-8 -*-
"""
-------------------------------------------------
   File Name：     test
   Description :
   Author :       Administrator
   date：          2019/4/3
-------------------------------------------------
   Change Activity:
                   2019/4/3:
-------------------------------------------------
"""
__author__ = 'Administrator'

import numpy as np
import pandas as pd
from sqlalchemy import create_engine
import datetime as dt
from sqlalchemy import types
import re
#%%
con = create_engine('mssql+pymssql://QRUser04:Passw0rdBP@10.86.166.13/BP_New_Factor')
conTL = create_engine('mssql+pymssql://QRUser04:Passw0rdBP@10.86.166.13/TLDB')

Today = dt.datetime.strftime(dt.datetime.today(), '%Y-%m-%d')
TLDB_Latest_Date = pd.read_sql('SELECT TOP (1) TRADE_DATE \
                               FROM TLDB.dbo.mkt_equd \
                               ORDER BY TRADE_DATE DESC', conTL).iloc[0, 0]
Factor_Latest_Date = pd.read_sql('SELECT TOP (1) TRADE_DATE \
                               FROM BP_New_Factor.dbo.ALPHA001 \
                               ORDER BY TRADE_DATE DESC', con).iloc[0, 0]
Factor_250_Date = pd.read_sql('SELECT TOP (260) TRADE_DATE \
                               FROM BP_New_Factor.dbo.ALPHA001 \
                               ORDER BY TICKER_SYMBOL,TRADE_DATE DESC', con).iloc[-1, 0]


# %% 定义基本函数

def rolling_corr(df1, df2, window):
    if df1.shape[0] != df2.shape[0]:
        df1 = df1.reindex(df2.index)
    return df1.rolling(window).corr(df2)


def rolling_rank(df1, window, percent=True):
    rank_list = []
    for start in range(df1.shape[0] - window + 1):
        end = start + window
        x = df1.iloc[start:end]
        rank = x.rank(axis=0, pct=percent).iloc[-1]
        rank_list.append(rank)
    rank_df = pd.concat(rank_list, axis=1).T.set_index(df1.index[window - 1:])
    return rank_df


def scale(df, n=1):
    return n * df.div(abs(df).sum(axis=1), axis=0)


def decay_linear(df, window):
    weight = np.array(range(1, window + 1))
    result_list = []
    for start in range(df.shape[0] - window + 1):
        end = start + window
        x = df.values[start:end]
        weighted_mean = np.average(x, axis=0, weights=weight)
        result_list.append(weighted_mean)
    result_df = pd.DataFrame(result_list, columns=df.columns).set_index(df.index[window - 1:])
    return result_df


# %% 获取后复权数据（不包含vwap)
market_af = pd.read_sql("SELECT a.TRADE_DATE, a.TICKER_SYMBOL, \
                   a.OPEN_PRICE_2 as 'open', a.HIGHEST_PRICE_2 as high, \
                   a.LOWEST_PRICE_2 as low, a.CLOSE_PRICE_2 as 'close', \
                   (b.TURNOVER_VOL / a.ACCUM_ADJ_FACTOR_2) as volume, b.NEG_MARKET_VALUE as nmkt \
                   FROM TLDB.dbo.mkt_equd_adj_af AS a, TLDB.dbo.mkt_equd AS b \
                   WHERE a.SECURITY_ID = b.SECURITY_ID \
                   and a.TRADE_DATE = b.TRADE_DATE \
                   and a.TRADE_DATE > '%s' \
                   and a.TRADE_DATE <= '%s' \
                   ORDER by TICKER_SYMBOL, TRADE_DATE" % (Factor_250_Date, TLDB_Latest_Date), conTL, index_col=['TRADE_DATE', 'TICKER_SYMBOL'])

open_af = market_af['open'].unstack()
close_af = market_af['close'].unstack()
high_af = market_af['high'].unstack()
low_af = market_af['low'].unstack()
volume_af = market_af['volume'].unstack()
nmkt = market_af['nmkt'].unstack()
ret = close_af.pct_change()
# market_af.to_csv('market_af.csv')
# %% TODO 共52个
# alpha001:
# (rank(Ts_ArgMax(SignedPower(((returns < 0) ? stddev(returns, 20) : close), 2.), 5)) -0.5)
ret_std = ret.rolling(20).std()
temp = ret_std.where(ret < 0, close_af) ** 2
ALPHA001 = (temp.rolling(5).apply(np.argmax, raw=True) + 1).rank(axis=1, pct=True)

# alpha002:
# (-1 * correlation(rank(delta(log(volume), 2)), rank(((close - open) / open)), 6))
temp = np.log(volume_af)
a = (temp - temp.shift(2)).rank(pct=True)
b = ((close_af - open_af) / open_af).rank(axis=1, pct=True)
ALPHA002 = -rolling_corr(a, b, 6)

# alpha003:
# (-1 * correlation(rank(open), rank(volume), 10))
a = open_af.rank(axis=1, pct=True)
b = volume_af.rank(axis=1, pct=True)
ALPHA003 = -rolling_corr(a, b, 10)

# alpha004:
# (-1 * Ts_Rank(rank(low), 9))
a = low_af.rank(axis=1, pct=True)
ALPHA004 = - rolling_rank(a, 9)

# alpha006:
# (-1 * correlation(open, volume, 10))
ALPHA006 = -rolling_corr(open_af, volume_af, 10)

# alpha007:
# ((adv20 < volume) ? ((-1 * ts_rank(abs(delta(close, 7)), 60)) * sign(delta(close, 7))) : (-1* 1))
adv20 = volume_af.rolling(20).mean()
delta = close_af - close_af.shift(7)
ts_rank = rolling_rank(abs(delta), 60)
temp = np.sign(delta)
ALPHA007 = (-ts_rank * temp).where(adv20 < volume_af, -1).where(~ts_rank.isna())

# alpha008:
# (-1 * rank(((sum(open, 5) * sum(returns, 5)) - delay((sum(open, 5) * sum(returns, 5)),10))))
a = open_af.rolling(5).sum() * ret.rolling(5).sum()
ALPHA008 = (a - a.shift(10)).rank(axis=1, pct=True)

# alpha009:
# ((0 < ts_min(delta(close, 1), 5)) ? delta(close, 1) : ((ts_max(delta(close, 1), 5) < 0) ?delta(close, 1) : (-1 * delta(close, 1))))
delta = close_af - close_af.shift()
a = delta.rolling(5).min() > 0
b = delta.rolling(5).max() < 0
ALPHA009 = delta.where(b, -delta)

# alpha010:
delta = close_af - close_af.shift()
cond_1 = delta.rolling(4).min() > 0
cond_2 = delta.rolling(4).max() < 0
ALPHA010 = delta.where(cond_1 | cond_2, -delta).rank(axis=1, pct=True)

# alpha012:
delta = close_af - close_af.shift()
delta_v = volume_af - volume_af.shift()
ALPHA012 = -np.sign(delta_v) * delta

# alpha013:
# (-1 * rank(correlation(rank(close), rank(volume), 5)))
a = close_af.rank(axis=1, pct=True)
b = volume_af.rank(axis=1, pct=True)
ALPHA013 = -rolling_corr(a, b, 5).rank(axis=1, pct=True)

# alpha014:
delta = ret - ret.shift(3)
ALPHA014 = delta.rank(axis=1, pct=True) * (-rolling_corr(open_af, volume_af, 10))

# alpha015:
# (-1 * sum(rank(correlation(rank(high), rank(volume), 3)), 3))
a = high_af.rank(axis=1, pct=True)
b = volume_af.rank(axis=1, pct=True)
ALPHA015 = -rolling_corr(a, b, 3).rank(axis=1, pct=True).rolling(3).sum()

# alpha016:
# (-1 * rank(covariance(rank(high), rank(volume), 5)))
a = high_af.rank(axis=1, pct=True)
b = volume_af.rank(axis=1, pct=True)
ALPHA016 = -rolling_corr(a, b, 5).rank(axis=1, pct=True)

# alpha017:
a = rolling_rank(close_af, 10).rank(axis=1, pct=True)
delta = close_af - close_af.shift()
b = (delta - delta.shift()).rank(axis=1, pct=True)
adv20 = volume_af.rolling(20).mean()
c = rolling_rank(volume_af / adv20, 5).rank(axis=1, pct=True)
ALPHA017 = -a * b * c

# alpha018:
a = abs(close_af - open_af).rolling(5).std()
b = close_af - open_af
c = rolling_corr(close_af, open_af, 10)
ALPHA018 = -(a + b + c).rank(axis=1, pct=True)

# alpha019:
delta = close_af - close_af.shift(7)
a = np.sign(delta)
b = (1 + ret.rolling(250).sum()).rank(axis=1, pct=True)
ALPHA019 = -a * b

# alpha020:
a = (open_af - high_af.shift()).rank(axis=1, pct=True)
b = (open_af - close_af.shift()).rank(axis=1, pct=True)
c = (open_af - low_af.shift()).rank(axis=1, pct=True)
ALPHA020 = -a * b * c

# alpha021:
# ((((sum(close, 8) / 8) + stddev(close, 8)) < (sum(close, 2) / 2)) ? -1 : (((sum(close,2) / 2) < ((sum(close, 8) / 8) - stddev(close, 8))) ? 1 : ((1 <= (volume / adv20)) ? 1 : -1)))
adv20 = volume_af.rolling(20).mean()
a = close_af.rolling(8).mean() + close_af.rolling(8).std()
b = close_af.rolling(2).mean()
c = close_af.rolling(8).mean() - close_af.rolling(8).std()
d = volume_af / adv20
e = pd.DataFrame(np.ones_like(close_af), index=close_af.index, columns=close_af.columns)
e[(b > a) | ((b >= c) & (b <= a) & (d < 1))] = -1
ALPHA021 = e.where(~a.isna())

# alpha022:
# (-1 * (delta(correlation(high, volume, 5), 5) * rank(stddev(close, 20))))
corr = rolling_corr(high_af, volume_af, 5)
a = corr - corr.shift(5)
b = close_af.rolling(20).std().rank(axis=1, pct=True)
ALPHA022 = -a * b

# alpha023:
# (((sum(high, 20) / 20) < high) ? (-1 * delta(high, 2)) : 0)
a = high_af.rolling(20).mean()
delta = high_af.shift(2) - high_af
delta[a >= high_af] = 0
ALPHA023 = delta

# alpha024:
# ((((delta((sum(close, 100) / 100), 100) / delay(close, 100)) <= 0.05)) ? (-1 * (close - ts_min(close,100))) : (-1 * delta(close, 3)))
temp = close_af.rolling(100).mean()
a = (temp - temp.shift(100)) / temp.shift(100)
c = close_af.rolling(100).min() - close_af
c[a > 0.05] = close_af.shift(3) - close_af
ALPHA024 = c

# alpha026:
# (-1 * ts_max(correlation(ts_rank(volume, 5), ts_rank(high, 5), 5), 3))
a = rolling_rank(volume_af, 5)
b = rolling_rank(high_af, 5)
ALPHA026 = -rolling_corr(a, b, 5).rolling(3).max()

# alpha028:
# scale(((correlation(adv20, low, 5) + ((high + low) / 2 - close) / close)) 有修改
adv20 = volume_af.rolling(20).mean()
ALPHA028 = scale(rolling_corr(adv20, low_af, 5) + ((high_af + low_af) / 2 - close_af) / close_af)

# alpha029:
# (min(rank(scale(log(ts_min(rank((-1 * rank(delta(close,5)))), 2)))), 5) + ts_rank(delay((-1 * returns), 6), 5))
delta = close_af - close_af.shift(5)
a = (-delta.rank(axis=1, pct=True)).rank(axis=1, pct=True).rolling(2).min()
b = rolling_rank(-ret - (-ret).shift(6), 5)
ALPHA029 = scale(np.log(a)).rank(axis=1, pct=True).rolling(5).min() + b

# alpha030:
# (((1.0 - rank(((sign((close - delay(close, 1))) + sign((delay(close, 1) - delay(close, 2)))) +sign((delay(close, 2) - delay(close, 3)))))) * sum(volume, 5)) / sum(volume, 20))
a = np.sign(close_af - close_af.shift(1))
b = np.sign(close_af.shift(1) - close_af.shift(2))
c = np.sign(close_af.shift(2) - close_af.shift(3))
d = volume_af.rolling(5).sum() / volume_af.rolling(10).sum()
ALPHA030 = (1 - (a + b + c).rank(axis=1, pct=True)) * d

# alpha031:
# ((rank(decay_linear((-1 * rank(delta(close, 10))), 10)) + rank((-1 *delta(close, 3)))) + sign(scale(correlation(adv20, low, 12))))
adv20 = volume_af.rolling(20).mean()
delta = close_af - close_af.shift(10)
a = decay_linear(-delta.rank(axis=1, pct=True), 10).rank(axis=1, pct=True)
b = (close_af.shift(3) - close_af).rank(axis=1, pct=True)
c = np.sign(scale(rolling_corr(adv20, low_af, 12)))
ALPHA031 = a + b + c

# alpha033:
# rank((-1 * ((1 - (open / close)))))
ALPHA033 = (-1 + (open_af / close_af)).rank(axis=1, pct=True)

# alpha034:
# rank(((1 - rank((stddev(returns, 2) / stddev(returns, 5)))) + (1 - rank(delta(close, 1)))))
a = ret.rolling(2).std() / ret.rolling(5).std()
b = close_af - close_af.shift()
ALPHA034 = (1 - a.rank(axis=1, pct=True) + 1 - b.rank(axis=1, pct=True)).rank(axis=1, pct=True)

# alpha035:
# ((Ts_Rank(volume, 32) * (1 - Ts_Rank(((close + high) - low), 16))) * (1 -Ts_Rank(returns, 32)))
a = rolling_rank(volume_af, 32)
b = rolling_rank((close_af + high_af) - low_af, 16)
c = rolling_rank(ret, 32)
ALPHA035 = a * (1 - b) * (1 - c)

# alpha037:
# (rank(correlation(delay((open - close), 1), close, 200)) + rank((open - close)))
delay = (open_af - close_af).shift()
a = rolling_corr(delay, close_af, 200)
b = open_af - close_af
ALPHA037 = a.rank(axis=1, pct=True) + b.rank(axis=1, pct=True)

# alpha038:
# ((-1 * rank(Ts_Rank(close, 10))) * rank((close / open)))
a = rolling_rank(close_af, 10).rank(axis=1, pct=True)
b = (close_af / open_af).rank(axis=1, pct=True)
ALPHA038 = -a * b

# alpha039:
# ((-1 * rank((delta(close, 7) * (1 - rank(decay_linear((volume / adv20), 9)))))) * (1 +rank(sum(returns, 250))))
delta = close_af - close_af.shift(7)
a = decay_linear(volume_af / adv20, 9)
b = ret.rolling(250).sum().rank(axis=1, pct=True)
ALPHA039 = -(delta * (1 - a.rank(axis=1, pct=True))).rank(axis=1, pct=True) * (1 + b)

# alpha040:
# ((-1 * rank(stddev(high, 10))) * correlation(high, volume, 10))
a = high_af.rolling(10).std().rank(axis=1, pct=True)
b = rolling_corr(high_af, volume_af, 10)
ALPHA040 = - a * b

# alpha043:
# (ts_rank((volume / adv20), 20) * ts_rank((-1 * delta(close, 7)), 8))
adv20 = volume_af.rolling(20).mean()
a = rolling_rank(volume_af / adv20, 20)
delta = close_af - close_af.shift(7)
b = rolling_rank(-delta, 8)
ALPHA043 = a * b

# alpha044:
# (-1 * correlation(high, rank(volume), 5))
ALPHA044 = -rolling_corr(high_af, volume_af.rank(axis=1, pct=True), 5)

# alpha045:
# (-1 * ((rank((sum(delay(close, 5), 20) / 20)) * correlation(close, volume, 2)) *rank(correlation(sum(close, 5), sum(close, 20), 2))))
a = close_af.rolling(20).mean().shift(5).rank(axis=1, pct=True)
b = rolling_corr(close_af, volume_af, 2)
c = rolling_corr(close_af.rolling(5).sum(), close_af.rolling(20).sum(), 2).rank(axis=1, pct=True)
ALPHA045 = a * b * c

# alpha046:
# ((0.25 < (((delay(close, 20) - delay(close, 10)) / 10) - ((delay(close, 10) - close) / 10))) ?(-1 * 1) : (((((delay(close, 20) - delay(close, 10)) / 10) - ((delay(close, 10) - close) / 10)) < 0) ? 1 :((-1 * 1) * (close - delay(close, 1)))))
a = (close_af.shift(20) - close_af.shift(10)) / 10
b = (close_af.shift(10) - close_af) / 10
c = close_af.shift() - close_af
d = pd.DataFrame(np.ones_like(close_af), index=close_af.index, columns=close_af.columns)
cond_1 = a - b > 0.25
cond_2 = ((a - b) >= 0) & ((a - b) <= 0.25)
d[cond_1] = -1
d[cond_2] = c
ALPHA046 = d.where(~a.isna())

# alpha049:
# (((((delay(close, 20) - delay(close, 10)) / 10) - ((delay(close, 10) - close) / 10)) < (-1 *0.1)) ? 1 : ((-1 * 1) * (close - delay(close, 1))))
a = (close_af.shift(20) - close_af.shift(10)) / 10 - (close_af.shift(10) - close_af) / 10
b = close_af.shift() - close_af
b[a < -0.1] = 1
ALPHA049 = b

# alpha051:
# (((((delay(close, 20) - delay(close, 10)) / 10) - ((delay(close, 10) - close) / 10)) < (-1 *0.05)) ? 1 : ((-1 * 1) * (close - delay(close, 1))))
a = (close_af.shift(20) - close_af.shift(10)) / 10 - (close_af.shift(10) - close_af) / 10
b = close_af.shift() - close_af
b[a < -0.05] = 1
ALPHA051 = b

# alpha052:
# ((((-1 * ts_min(low, 5)) + delay(ts_min(low, 5), 5)) * rank(((sum(returns, 240) -sum(returns, 20)) / 220))) * ts_rank(volume, 5))
a = low_af.rolling(5).min()
b = (ret.rolling(240).sum() - ret.rolling(20).sum()) / 220
c = rolling_rank(volume_af, 5)
ALPHA052 = (a.shift(5) - a) * b.rank(axis=1, pct=True) * c

# alpha053:
# (-1 * delta((((close - low) - (high - close)) / (close - low)), 9))
a = 2 * close_af - low_af - high_af
b = close_af - low_af
ALPHA053 = (a / b).shift(9) - a / b

# alpha054:
# ((-1 * ((low - close) * (open^5))) / ((low - high) * (close^5)))
ALPHA054 = (low_af - close_af) * open_af ** 5 / ((low_af - high_af) * close_af ** 5)

# alpha055:
# (-1 * correlation(rank(((close - ts_min(low, 12)) / (ts_max(high, 12) - ts_min(low,12)))), rank(volume), 6))
a = close_af - low_af.rolling(12).min()
b = high_af.rolling(12).max() - low_af.rolling(12).min()
ALPHA055 = -rolling_corr((a / b).rank(axis=1, pct=True), volume_af.rank(axis=1, pct=True), 6)

# alpha056:
# (0 - (1 * (rank((sum(returns, 10) / sum(sum(returns, 2), 3))) * rank((returns * cap)))))
a = ret.rolling(10).sum()
b = (ret.rolling(2).sum()).rolling(3).sum()
c = (ret * nmkt).rank(axis=1, pct=True)
ALPHA056 = -(a / b).rank(axis=1, pct=True) * c

# alpha060:
# (0 - (1 * ((2 * scale(rank(((((close - low) - (high - close)) / (high - low)) * volume)))) -scale(rank(ts_argmax(close, 10))))))
a = 2 * close_af - low_af - high_af
b = high_af - low_af
c = close_af.rolling(10).apply(np.argmax) + 1
ALPHA060 = -(2 * scale(((a / b) * volume_af).rank(axis=1, pct=True)) - scale(c))

# alpha068:
# ((Ts_Rank(correlation(rank(high), rank(adv15), 8.91644), 13.9333) <rank(delta(((close * 0.518371) + (low * (1 - 0.518371))), 1.06157))) * -1)
adv15 = volume_af.rolling(15).mean()
a = high_af.rank(axis=1, pct=True)
b = adv15.rank(axis=1, pct=True)
x = rolling_rank(rolling_corr(a, b, 8), 13)
c = close_af * 0.518371 + low_af * (1 - 0.518371)
y = (c - c.shift()).rank(axis=1, pct=True).reindex(x.index)
ALPHA068 = -1 * (x < y).where(~x.isna())

# alpha085:
# (rank(correlation(((high * 0.876703) + (close * (1 - 0.876703))), adv30,9.61331))^rank(correlation(Ts_Rank(((high + low) / 2), 3.70596), Ts_Rank(volume, 10.1595),7.11408)))
adv30 = volume_af.rolling(30).mean()
a = high_af * 0.876703 + close_af * (1 - 0.876703)
b = rolling_rank((high_af + low_af) / 2, 3)
c = rolling_rank(volume_af, 10)
ALPHA085 = rolling_corr(a, adv30, 9).rank(axis=1, pct=True) ** (rolling_corr(b, c, 7).rank(axis=1, pct=True))

# alpha088:
# min(rank(decay_linear(((rank(open) + rank(low)) - (rank(high) + rank(close))),8.06882)), Ts_Rank(decay_linear(correlation(Ts_Rank(close, 8.44728), Ts_Rank(adv60,20.6966), 8.01266), 6.65053), 2.61957))
adv60 = volume_af.rolling(60).mean()
a = open_af.rank(axis=1, pct=True) + low_af.rank(axis=1, pct=True) - high_af.rank(axis=1, pct=True) - close_af.rank(
    axis=1, pct=True)
b = rolling_rank(close_af, 8)
c = rolling_rank(adv60, 20)
x = decay_linear(a, 8).rank(axis=1, pct=True)
y = rolling_rank(decay_linear(rolling_corr(b, c, 8), 6), 2).reindex(x.index)
ALPHA088 = y.where(y < x, x)

# alpha095:
# (rank((open - ts_min(open, 12.4105))) < Ts_Rank((rank(correlation(sum(((high + low)/ 2), 19.1351), sum(adv40, 19.1351), 12.8742))^5), 11.7584))
adv40 = volume_af.rolling(40).mean()
x = (open_af - open_af.rolling(12).min()).rank(axis=1, pct=True)
b = (high_af + low_af) / 2
c = adv40.rolling(19).sum()
a = rolling_corr(b.rolling(19).sum(), c, 12).rank(axis=1, pct=True) ** 5
y = rolling_rank(a, 11).reindex(x.index)
ALPHA095 = 1 * (x < y).where(~y.isna())

# alpha099:
# ((rank(correlation(sum(((high + low) / 2), 19.8975), sum(adv60, 19.8975), 8.8136)) <rank(correlation(low, volume, 6.28259))) * -1)
adv60 = volume_af.rolling(60).mean()
a = ((high_af + low_af) / 2).rolling(19).sum()
b = adv60.rolling(19).sum()
x = rolling_corr(a, b, 8).rank(axis=1, pct=True)
y = (-rolling_corr(low_af, volume_af, 6)).rank(axis=1, pct=True).reindex(x.index)
ALPHA099 = 1 * (x < y).where(~x.isna())

# alpha101:
# ((close - open) / ((high - low) + .001))
ALPHA101 = (close_af - open_af) / (high_af - low_af + 0.001)

# %%
variable = ' '.join(dir())
talist = re.findall(r'\b[A-Z][0-9A-Z_]*\b', variable)

with pd.option_context('mode.use_inf_as_na', True):
    for indi in talist:
        indi_df = eval(indi)[eval(indi).index > Factor_Latest_Date].stack().rename(indi).reset_index()
        # indi_df = eval(indi)[eval(indi).index == '2019-08-21'].stack().rename(indi).reset_index()

        # indi_df.to_csv('%s.csv' % indi, index=False)
        indi_df.to_sql(indi, con, index=False, dtype={'TRADE_DATE': types.DATE}, if_exists='append')



